﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace DataBase
{
    public class IngresoEgresoBD
    {
        static string conexion = "Data Source=JCIDatabase.db.10200093.hostedresource.com; Initial Catalog=JCIDatabase; User ID=JCIDatabase; Password='ucbJCI2012!'";
        public static ICollection getGestiones(int gestion, DateTime inicio, DateTime fin)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlDataAdapter comando = new SqlDataAdapter();
                if (gestion > 0)
                {
                    comando.SelectCommand = new SqlCommand("Select Gestion,FechaCreacion From [Manejo] Where Gestion=@gest", canal);
                    comando.SelectCommand.Parameters.AddWithValue("gest",gestion);
                }
                else
                {
                    comando.SelectCommand = new SqlCommand("Select Gestion,FechaCreacion From [Manejo] Where FechaCreacion>=@ini And FechaCreacion<=@fin", canal);
                    comando.SelectCommand.Parameters.AddWithValue("ini", inicio);
                    comando.SelectCommand.Parameters.AddWithValue("fin", fin);
                }
                
                canal.Open();
                DataTable dt = new DataTable();
                dt.Columns.Add(new DataColumn("Gestion", typeof(int)));
                dt.Columns.Add(new DataColumn("FechaCreacion", typeof(DateTime)));
                
                comando.Fill(dt);
               /* foreach (DataRow row in dt.Rows)
                {
                    if (Convert.ToBoolean(row["Estado"]))
                        row["EstadoTexto"] = "Abierto";
                    else
                        row["EstadoTexto"] = "Cerrado";
                }*/
                DataView dv = new DataView(dt);
                
                
                return dv;


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static ICollection getDatos(int gestion,char tipo)
        { 
             try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlDataAdapter comando = new SqlDataAdapter();
                comando.SelectCommand = new SqlCommand("Select (Valor*Cantidad) as Total,Fecha from [IngresoEgreso],[Evento] where IngresoEgreso.Gestion=@ges and Evento.Tipo=@tipo and Evento.Codigo=IngresoEgreso.Evento", canal);
                comando.SelectCommand.Parameters.AddWithValue("ges", gestion);
                comando.SelectCommand.Parameters.AddWithValue("tipo", tipo);

                canal.Open();
                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("Total",typeof(float)));
                table.Columns.Add(new DataColumn("Fecha", typeof(DateTime)));

                comando.Fill(table);
                DataView dv = new DataView(table);
                return dv;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        
        }
        public static bool verificarExistencia(int gestion)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Select Gestion From [Manejo] where Gestion=@ges";
                comando.Parameters.AddWithValue("ges", gestion);
                canal.Open();
                SqlDataReader reader = comando.ExecuteReader();
                return reader.HasRows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static ICollection getIngresoEgreso(int gestion, char tipo)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlDataAdapter comando = new SqlDataAdapter();
                comando.SelectCommand = new SqlCommand("SELECT E.Nombre,E.Codigo FROM PresupuestoUsado AS P INNER JOIN Evento AS E ON P.CodigoIE = E.Codigo WHERE (P.GestionPresupuesto = @ges) AND (E.Tipo = @tipo)", canal);
                comando.SelectCommand.Parameters.AddWithValue("ges", gestion);
                comando.SelectCommand.Parameters.AddWithValue("tipo", tipo);

                canal.Open();
                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("Nombre",typeof(String)));
                table.Columns.Add(new DataColumn("Codigo", typeof(long)));

                comando.Fill(table);
                DataView dv = new DataView(table);
                return dv;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static ICollection mostrarTodosIE(int gestion,char tipo,ref double res)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlDataAdapter comando = new SqlDataAdapter();
                comando.SelectCommand = new SqlCommand("Select I.Nombre,I.Valor,I.Cantidad,(I.Valor*I.Cantidad) as Total,I.Fecha From IngresoEgreso as I,Evento as E Where I.Gestion=@ges and E.Tipo=@tip and I.Evento=E.Codigo", canal);
                comando.SelectCommand.Parameters.AddWithValue("ges", gestion);
                comando.SelectCommand.Parameters.AddWithValue("tip", tipo);
                canal.Open();
                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("Nombre", typeof(String)));
                table.Columns.Add(new DataColumn("Valor", typeof(float)));
                table.Columns.Add(new DataColumn("Cantidad", typeof(int)));
                table.Columns.Add(new DataColumn("Total", typeof(float)));
                table.Columns.Add(new DataColumn("Fecha", typeof(DateTime)));
                comando.Fill(table);
                res = 0;
                foreach (DataRow row in table.Rows)
                {
                    res += Convert.ToDouble(row["Total"]);
                }
                DataView dv = new DataView(table);
                return dv;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        
        }
        public static ICollection mostrarIE(int gestion,long evento)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlDataAdapter comando = new SqlDataAdapter();
                comando.SelectCommand = new SqlCommand("Select I.Nombre,I.Valor,I.Cantidad,(I.Valor*I.Cantidad) as Total,I.Fecha From IngresoEgreso as I Where I.Gestion=@ges and I.Evento=@even", canal);
                comando.SelectCommand.Parameters.AddWithValue("ges", gestion);
                comando.SelectCommand.Parameters.AddWithValue("even", evento);
                canal.Open();
                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("Nombre", typeof(String)));
                table.Columns.Add(new DataColumn("Valor", typeof(float)));
                table.Columns.Add(new DataColumn("Cantidad", typeof(int)));
                table.Columns.Add(new DataColumn("Total", typeof(float)));
                table.Columns.Add(new DataColumn("Fecha", typeof(DateTime)));
                comando.Fill(table);
                DataView dv = new DataView(table);
                return dv;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void Actualizar(string nombre, float valor, int cantidad, DateTime fecha,int gestion,long evento,float inicial)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Update [IngresoEgreso] Set Valor=@val,Cantidad=@can,Fecha=@fec Where Nombre=@nom";
                comando.Parameters.AddWithValue("val", valor);
                comando.Parameters.AddWithValue("can", cantidad);
                comando.Parameters.AddWithValue("fec", fecha);
                comando.Parameters.AddWithValue("nom", nombre);
                canal.Open();
                comando.ExecuteNonQuery();
                DataBase.Presupuesto.BDPresupuesto.incrementarUsado(gestion, evento, inicial * (-1));
                DataBase.Presupuesto.BDPresupuesto.incrementarUsado(gestion, evento, valor*cantidad);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void Eliminar(string nombre,float total,int gestion,long evento)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Delete From [IngresoEgreso] Where Nombre=@nom";
                comando.Parameters.AddWithValue("nom", nombre);
                canal.Open();
                comando.ExecuteNonQuery();
                DataBase.Presupuesto.BDPresupuesto.incrementarUsado(gestion, evento, total*(-1));
            }
            catch (Exception)
            {
                throw new Exception("Error al eliminar");
            }
        }
        public static ICollection getValores(int gestion,char tipo)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlDataAdapter comando = new SqlDataAdapter();
                comando.SelectCommand = new SqlCommand("Select Distinct E.Codigo,E.Nombre,P.Usado,(P.ValorUnitario*P.Cantidad) as Total From Evento as E,PresupuestoUsado as P Where E.Tipo=@tipo And P.GestionPresupuesto=@ges and P.CodigoIE=E.Codigo", canal);
                comando.SelectCommand.Parameters.AddWithValue("ges", gestion);
                comando.SelectCommand.Parameters.AddWithValue("tipo", tipo);  
                canal.Open();
                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("Nombre", typeof(String)));
                table.Columns.Add(new DataColumn("Usado", typeof(float)));
                table.Columns.Add(new DataColumn("Total", typeof(float)));
                table.Columns.Add(new DataColumn("Codigo", typeof(long)));
                comando.Fill(table);
                DataView dv = new DataView(table);
                return dv;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static bool verificarTipo(long codigo)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Select Codigo From Evento as E where Codigo=@cod And Tipo='E'";
                comando.Parameters.AddWithValue("cod", codigo);
               
                canal.Open();
                SqlDataReader reader = comando.ExecuteReader();
                return reader.HasRows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        
        }
        public static bool verificarExceso(int gestion,long codigo,float total)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Select P.Usado From PresupuestoUsado as P where GestionPresupuesto=@ges and CodigoIE=@cod And (Usado+@tot>(Valor*Cantidad))";
                comando.Parameters.AddWithValue("ges", gestion);
                comando.Parameters.AddWithValue("cod", codigo);
                comando.Parameters.AddWithValue("tot", total);

                canal.Open();
                SqlDataReader reader = comando.ExecuteReader();
                return reader.HasRows;
            }
            catch (Exception ex)
            {
                throw ex;
            }   
        }
        public static void insertarIE(string nombre,int gestion,long codigo, float valor, int cantidad, DateTime fecha)
        {
            try
            {
                if (verificarTipo(codigo))
                {
                    if (verificarExceso(gestion, codigo, valor * cantidad))
                        throw new Exception("no puedes exceder el Presupuesto asignado");
                }
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Insert Into [IngresoEgreso](Nombre,Gestion,Evento,Valor,Cantidad,Fecha) Values (@nom,@ges,@even,@val,@can,@fec)";
                comando.Parameters.AddWithValue("nom", nombre);
                comando.Parameters.AddWithValue("ges", gestion);
                comando.Parameters.AddWithValue("even", codigo);
                comando.Parameters.AddWithValue("val", valor);
                comando.Parameters.AddWithValue("can", cantidad);
                comando.Parameters.AddWithValue("fec", fecha);
                canal.Open();
                comando.ExecuteNonQuery();
                DataBase.Presupuesto.BDPresupuesto.incrementarUsado(gestion, codigo, cantidad*valor);
            }
            catch (SqlException)
            {
                throw new Exception("Ya registraste este nombre");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void CrearIngresoEgreso(int gestion,DateTime creacion)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Insert Into [Manejo](Gestion,FechaCreacion) Values (@ges,@fec)";
                comando.Parameters.AddWithValue("ges", gestion);
                comando.Parameters.AddWithValue("fec", creacion);
                canal.Open();
                comando.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
    }
}
